using System;
using System.Collections.Generic;
using System.Data;
using MySql.Data.MySqlClient;
using WegasManagementSystem.Models;

namespace WegasManagementSystem.DAL
{
    /// <summary>
    /// 部门数据访问类
    /// </summary>
    public class DepartmentDAL
    {
        /// <summary>
        /// 添加部门
        /// </summary>
        /// <param name="department">部门对象</param>
        /// <returns>新部门ID，失败返回0</returns>
        public int AddDepartment(Department department)
        {
            string sql = @"INSERT INTO org_department (dept_name, dept_code, parent_id, manager_id, created_time, updated_time) 
                          VALUES (@dept_name, @dept_code, @parent_id, @manager_id, @created_time, @updated_time);
                          SELECT LAST_INSERT_ID();";

            MySqlParameter[] parameters = new MySqlParameter[]
            {
                new MySqlParameter("@dept_name", department.DeptName),
                new MySqlParameter("@dept_code", department.DeptCode),
                new MySqlParameter("@parent_id", department.ParentId.HasValue ? (object)department.ParentId.Value : DBNull.Value),
                new MySqlParameter("@manager_id", department.ManagerId.HasValue ? (object)department.ManagerId.Value : DBNull.Value),
                new MySqlParameter("@created_time", department.CreatedTime),
                new MySqlParameter("@updated_time", department.UpdatedTime)
            };

            object result = DBConnection.ExecuteScalar(sql, CommandType.Text, parameters);
            return result != null ? Convert.ToInt32(result) : 0;
        }

        /// <summary>
        /// 更新部门信息
        /// </summary>
        /// <param name="department">部门对象</param>
        /// <returns>更新成功返回true，失败返回false</returns>
        public bool UpdateDepartment(Department department)
        {
            string sql = @"UPDATE org_department 
                          SET dept_name = @dept_name, dept_code = @dept_code, 
                              parent_id = @parent_id, manager_id = @manager_id, updated_time = @updated_time
                          WHERE dept_id = @dept_id";

            MySqlParameter[] parameters = new MySqlParameter[]
            {
                new MySqlParameter("@dept_id", department.DeptId),
                new MySqlParameter("@dept_name", department.DeptName),
                new MySqlParameter("@dept_code", department.DeptCode),
                new MySqlParameter("@parent_id", department.ParentId.HasValue ? (object)department.ParentId.Value : DBNull.Value),
                new MySqlParameter("@manager_id", department.ManagerId.HasValue ? (object)department.ManagerId.Value : DBNull.Value),
                new MySqlParameter("@updated_time", DateTime.Now)
            };

            int result = DBConnection.ExecuteNonQuery(sql, CommandType.Text, parameters);
            return result > 0;
        }

        /// <summary>
        /// 删除部门
        /// </summary>
        /// <param name="deptId">部门ID</param>
        /// <returns>删除成功返回true，失败返回false</returns>
        public bool DeleteDepartment(int deptId)
        {
            // 检查是否有子部门或员工使用了该部门
            string checkSql = @"SELECT COUNT(*) FROM org_department WHERE parent_id = @dept_id
                               UNION ALL
                               SELECT COUNT(*) FROM emp_employee WHERE dept_id = @dept_id";
            
            MySqlParameter checkParam = new MySqlParameter("@dept_id", deptId);
            DataTable dt = DBConnection.ExecuteDataTable(checkSql, CommandType.Text, checkParam);
            
            if (dt != null && dt.Rows.Count > 0)
            {
                if (Convert.ToInt32(dt.Rows[0][0]) > 0 || Convert.ToInt32(dt.Rows[1][0]) > 0)
                {
                    throw new Exception("该部门下有子部门或员工，无法删除！");
                }
            }

            string sql = "DELETE FROM org_department WHERE dept_id = @dept_id";
            MySqlParameter[] parameters = new MySqlParameter[]
            {
                new MySqlParameter("@dept_id", deptId)
            };

            int result = DBConnection.ExecuteNonQuery(sql, CommandType.Text, parameters);
            return result > 0;
        }

        /// <summary>
        /// 获取所有部门
        /// </summary>
        /// <returns>部门列表</returns>
        public List<Department> GetAllDepartments()
        {
            string sql = "SELECT * FROM org_department ORDER BY dept_id";
            DataTable dt = DBConnection.ExecuteDataTable(sql);
            List<Department> departments = new List<Department>();

            if (dt != null && dt.Rows.Count > 0)
            {
                foreach (DataRow row in dt.Rows)
                {
                    departments.Add(MapRowToDepartment(row));
                }
            }

            return departments;
        }

        /// <summary>
        /// 根据ID获取部门
        /// </summary>
        /// <param name="deptId">部门ID</param>
        /// <returns>部门对象</returns>
        public Department GetDepartmentById(int deptId)
        {
            string sql = "SELECT * FROM org_department WHERE dept_id = @dept_id";
            MySqlParameter[] parameters = new MySqlParameter[]
            {
                new MySqlParameter("@dept_id", deptId)
            };

            DataTable dt = DBConnection.ExecuteDataTable(sql, CommandType.Text, parameters);
            if (dt != null && dt.Rows.Count > 0)
            {
                return MapRowToDepartment(dt.Rows[0]);
            }
            return null;
        }

        /// <summary>
        /// 检查部门编码是否存在
        /// </summary>
        /// <param name="deptCode">部门编码</param>
        /// <param name="excludeDeptId">排除的部门ID</param>
        /// <returns>存在返回true，不存在返回false</returns>
        public bool CheckDeptCodeExists(string deptCode, int excludeDeptId = 0)
        {
            string sql = "SELECT COUNT(*) FROM org_department WHERE dept_code = @dept_code";
            List<MySqlParameter> parameters = new List<MySqlParameter>
            {
                new MySqlParameter("@dept_code", deptCode)
            };

            if (excludeDeptId > 0)
            {
                sql += " AND dept_id <> @dept_id";
                parameters.Add(new MySqlParameter("@dept_id", excludeDeptId));
            }

            object result = DBConnection.ExecuteScalar(sql, CommandType.Text, parameters.ToArray());
            return result != null && Convert.ToInt32(result) > 0;
        }

        /// <summary>
        /// 获取部门树形结构
        /// </summary>
        /// <returns>顶级部门列表（每个部门包含其子部门）</returns>
        public List<Department> GetDepartmentTree()
        {
            // 这里只返回简单列表，前端可以根据ParentId自行构建树形结构
            return GetAllDepartments();
        }

        /// <summary>
        /// 根据部门编号获取部门信息
        /// </summary>
        /// <param name="deptNumber">部门编号</param>
        /// <returns>部门信息对象</returns>
        public Department GetDepartmentByNumber(string deptNumber)
        {
            if (string.IsNullOrEmpty(deptNumber))
            {
                return null;
            }

            string sql = "SELECT * FROM org_department WHERE dept_code = @dept_code";
            MySqlParameter[] parameters = new MySqlParameter[]
            {
                new MySqlParameter("@dept_code", deptNumber)
            };

            DataTable dt = DBConnection.ExecuteDataTable(sql, CommandType.Text, parameters);
            if (dt != null && dt.Rows.Count > 0)
            {
                return MapRowToDepartment(dt.Rows[0]);
            }
            
            return null;
        }

        /// <summary>
        /// 将DataRow映射到Department对象
        /// </summary>
        /// <param name="row">DataRow</param>
        /// <returns>Department对象</returns>
        private Department MapRowToDepartment(DataRow row)
        {
            Department department = new Department();
            department.DeptId = Convert.ToInt32(row["dept_id"]);
            department.DeptName = row["dept_name"].ToString();
            department.DeptCode = row["dept_code"].ToString();
            
            if (row["parent_id"] != DBNull.Value)
                department.ParentId = Convert.ToInt32(row["parent_id"]);
            
            if (row["manager_id"] != DBNull.Value)
                department.ManagerId = Convert.ToInt32(row["manager_id"]);
            
            department.CreatedTime = Convert.ToDateTime(row["created_time"]);
            department.UpdatedTime = Convert.ToDateTime(row["updated_time"]);

            return department;
        }
    }
}